<?php

class Db{
    public $conn;
    protected $dbhost,$dbuser,$dbpwd,$dbname;
    function __construct($dbhost,$dbuser,$dbpwd,$dbname)
    {
        $this->conn = mysqli_connect($dbhost,$dbuser,$dbpwd);
    	if($this->conn==false){
	   return false;	
	}
	$this->init();
	
        mysqli_query($this->conn,'SET NAMES utf8');
    }

    function init(){
        $sql="show databases like 'ddcdata'";
        $res = $this->query($sql);
        if(!$res->num_rows){
            $sql = "create database if not exists ddcdata Character Set UTF8";
            $this->query($sql);
        }

        mysqli_select_db($this->conn,'ddcdata');

        $sql="show tables like 'gpsinfo'";
        $res = $this->query($sql);
        if(!$res->num_rows){
            $sql = "CREATE TABLE If Not Exists `gpsinfo` (
                      `id` int(11) NOT NULL AUTO_INCREMENT,
                      `sid` char(16) NOT NULL DEFAULT '0',
                      `layerid` tinyint(4) NOT NULL DEFAULT '0',
                      `areaid` tinyint(4) NOT NULL DEFAULT '0',
                      `point_type` tinyint(4) DEFAULT NULL COMMENT '夯击类型 1满夯 2点夯 3置换',
                      `shape_type` tinyint(4) DEFAULT NULL COMMENT '形状',
                      `point_hash` varchar(32) DEFAULT NULL,
                      `lon` double NOT NULL DEFAULT '0',
                      `lat` double NOT NULL DEFAULT '0',
                      `hi` double NOT NULL DEFAULT '0',
                      `high` double DEFAULT NULL,
                      `drct` double NOT NULL DEFAULT '-1',
                      `nums` int(8) NOT NULL DEFAULT '0',
                      `dmod` tinyint(4) NOT NULL DEFAULT '1',
                      `gps_time` char(32) NOT NULL,
                      `update_time` char(32) NOT NULL,
                    PRIMARY KEY (`id`),
                    INDEX `sid` USING BTREE (sid)
                ) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0";
            $this->query($sql);

            $sql = "CREATE TABLE If Not Exists `flag` (
                      `flag` char(16) DEFAULT '0',
                      `val` int(9) DEFAULT '0',
                      `update_time` char(24) DEFAULT NULL
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
            $this->query($sql);

            $sql = "insert into flag (flag,val) VALUE ('upload',0),('download',0),('layerid',0),('areaid',0),('sid',0),('diameter',240),('shape_type',0),('point_type',0)";
            $this->query($sql);

            $sql = "CREATE TABLE If Not Exists `carlist` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `sid` varchar(64) NOT NULL,
                `name` varchar(64) NOT NULL,
                `type` tinyint(4) NOT NULL,
                `master_paver` tinyint(4) NOT NULL DEFAULT '0',
                `width` int(11) NOT NULL DEFAULT '220',
                `offset_x` int(11) NOT NULL DEFAULT '0',
                `offset_y` int(11) NOT NULL DEFAULT '0',
                `lat` double NOT NULL DEFAULT '0',
                `lon` double NOT NULL DEFAULT '0',
                `hi` double NOT NULL  DEFAULT '0',
                `drct` double NOT NULL DEFAULT '0',
                `mile` double NOT NULL DEFAULT '0',
                `layerid` double NOT NULL DEFAULT '0',
                `dmod` tinyint(4) NOT NULL DEFAULT '0',
                `speed` double NOT NULL DEFAULT '0',
                `vcv` double NOT NULL DEFAULT '0',
                `section` double NOT NULL DEFAULT '0',
                `gps_time` char(32) NOT NULL DEFAULT '',
                `paver_layer_id` tinyint(4) NOT NULL DEFAULT '0',
                `areaid` tinyint(4) NOT NULL DEFAULT '0',
                `update_time` varchar(20) NOT NULL,
                PRIMARY KEY (`id`)
            ) ENGINE=`Myisam` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;";           
	$this->query($sql);

            $sql = "CREATE TABLE If Not Exists `layer` (
                      `id` int(9) NOT NULL,
                      `layer_name` varchar(255) DEFAULT NULL,
                      `subject_id` int(9) NOT NULL,
                      `point_type` varchar(4) NOT NULL COMMENT '工艺',
                      `shape_type` varchar(255) DEFAULT NULL COMMENT '形状',
                      `sort` varchar(4) NOT NULL ,
                      `size` double DEFAULT '210' COMMENT '夯点大小',
                      `area_margin` double DEFAULT NULL COMMENT '夯点边距',
                      `height` double DEFAULT NULL COMMENT '拉高',
                      `tech` varchar(512) NOT NULL DEFAULT '',
                      `add_time` varchar(20) NOT NULL DEFAULT '',
                      `status` tinyint(4) NOT NULL DEFAULT '1',
                      `update_time` varchar(20) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=`Myisam` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;";
            $this->query($sql);

            $sql = "CREATE TABLE If Not Exists `area` (
                      `id` int(9) NOT NULL,
                      `subject_id` int(9) NOT NULL,
                      `area_name` varchar(255) DEFAULT NULL,
                      `sort` varchar(255) DEFAULT NULL,
                      `status` tinyint(4) NOT NULL DEFAULT '1',
                      `update_time` varchar(20) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=`Myisam` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;";
            $this->query($sql);
			
			echo "created database\n\r";
        }else{
            //清除历史记录
            $timelimit = date('Y-m-d H:i:s', time()-3600*24*3);
            $sql = "delete from gpsinfo where gps_time<'$timelimit' and id<(select val from flag where flag='upload')";
            $this->query($sql);

            //$sql = " update flag set val=0 where flag='download' ";
            //$this->query($sql);

        }

    }

    function query($sql){
        $res = mysqli_query($this->conn,$sql);
        if(!$res){
            echo "\r\nERR:".mysqli_error($this->conn);
            echo "\r\n ".$sql;
        }
        return $res;
    }

    function select($sql){
        $result = $this->query($sql);
        $rows=[];
        if($result){
            while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
                $rows[]=$row;
            }
        }
        return $rows;
    }


    function update($table,$data,$where,$set=0){
        $sql = "update $table set ";
        foreach($data as $k=>$v){
            $sql.=$k.'="'.$v.'",';
        }
        $sql = substr($sql,0,-1)." where $where";
        $res = $this->query($sql);

        $affected_row = mysqli_affected_rows($this->conn);

        if(!$set || $affected_row){
            return $res;
        }

        if(!$affected_row){
            $sql="select * from $table where $where";
            if(!$this->select($sql)){
                $res = $this->insert($table,$data);
            }
        }

        return $res;
    }

    function insert($table,$data){
        $sql = "insert into $table (";
        $vals="";
        foreach ($data as $k=>$v){
            $sql.="`$k`,";
            $vals.="'$v',";
        }
        $sql = substr($sql,0,-1).') values ('. substr($vals,0,-1).')';
        return $this->query($sql);
    }
}

$dbhost='127.0.0.1';
$dbuser='root';
$dbpwd= 'root';
$dbname='ddcdata';

while(1){
	$db = new Db($dbhost, $dbuser, $dbpwd, $dbname);
	if($db->conn!=false){
	   break;
	}
	sleep(1);
}
